﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace Aj.Core
{

    public sealed class SqlManage
    {
        private static readonly SqlManage sqlManage = null;

        private string connString;

        private OleDbConnection conn;

        private static object locker = new object();

        private SqlManage() { }

        static SqlManage()
        {
            if (null == sqlManage)
            {
                lock (locker)
                {
                    if (null == sqlManage)
                    {
                        sqlManage = new SqlManage();
                        sqlManage.connString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
                        sqlManage.conn = new OleDbConnection(sqlManage.connString);
                    }
                }
            }
        }

        /// <summary>
        /// 获取实例(通过单例模式，用静态构造函数实例对象)
        /// </summary>
        /// <param name="connConfigString">在Web.config文件中配置的数据库链接的KEY</param>
        /// <returns>返回SqlManage实例</returns>
        public static SqlManage GetInstance()
        {
            //SqlManage newDemond = new SqlManage();
            //newDemond.connString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
            //newDemond.conn = new OleDbConnection(newDemond.connString);
            return sqlManage;
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private void OpenConn()
        {
            if (this.conn.State == ConnectionState.Closed)
            {
                this.conn.Open();
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private void Dispose()
        {
            if (this.conn.State != ConnectionState.Closed)
            {
                this.conn.Close();
            }
        }

        /// <summary>
        /// 返回第一行第一列的数据
        /// </summary>
        /// <param name="_commandtype"></param>
        /// <param name="_commandtext"></param>
        /// <param name="_parameters"></param>
        /// <returns></returns>
        public object GetFistColumn(CommandType _commandtype, string _commandtext, params OleDbParameter[] _parameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = _commandtype;
            cmd.CommandText = _commandtext;
            cmd.Connection = this.conn;

            if (null != _parameters)
            {
                foreach (OleDbParameter para in _parameters)
                {
                    cmd.Parameters.Add(para);
                }
            }
            try
            {
                OpenConn();
                object obj = cmd.ExecuteScalar();
                Dispose();
                return obj;
            }
            catch (Exception ex)
            {
                Function.WriteErrorLog("数据库连接错误或表不存在，请检查！" + ex.Message);
                return null;
            }
            finally
            {
                cmd.Dispose();
                Dispose();
            }
        }

        /// <summary>
        /// 执行sql命令
        /// </summary>
        /// <param name="_commandtype"></param>
        /// <param name="_commandtext"></param>
        /// <param name="_parameters"></param>
        /// <returns></returns>
        public int ExecuteSql(CommandType _commandtype, string _commandtext, params OleDbParameter[] _parameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = _commandtype;
            cmd.CommandText = _commandtext;
            cmd.Connection = this.conn;
            if (null != _parameters)
            {
                foreach (OleDbParameter para in _parameters)
                {
                    cmd.Parameters.Add(para);
                }
            }
            try
            {
                OpenConn();
                int n = cmd.ExecuteNonQuery();
                Dispose();
                return n;
            }
            catch (Exception ex)
            {
                Function.WriteErrorLog("数据库连接错误或传入空SqlParameter参数，请检查！" + ex.Message);
                return 0;
            }
            finally
            {
                cmd.Dispose();
                Dispose();
            }
        }

        /// <summary>
        /// 执行带事物的命令集
        /// </summary>
        /// <param name="_tran"></param>
        /// <param name="_commandtype"></param>
        /// <param name="_commandtext"></param>
        /// <param name="_parameters"></param>
        /// <returns></returns>
        public int ExecuteSql(List<CommandType> _commandtype, List<string> _commandtext, List<OleDbParameter[]> _parameters)
        {
            if (_commandtype.Count != _commandtext.Count)
                return 0;

            if (null != _parameters)
            {
                if (_parameters.Count != _commandtext.Count)
                    return 0;
            }

            int r = 0;
            List<OleDbCommand> listCmd = new List<OleDbCommand>();
            for (int i = 0; i < _commandtype.Count; i++)
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = _commandtype[i];
                cmd.CommandText = _commandtext[i];
                cmd.Connection = this.conn;

                if (null != _parameters)
                {
                    if (null != _parameters[i])
                    {
                        foreach (OleDbParameter para in _parameters[i])
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                }

                listCmd.Add(cmd);
            }

            OpenConn();
            using (OleDbTransaction tran = conn.BeginTransaction())
            {
                try
                {
                    foreach (OleDbCommand c in listCmd)
                    {
                        c.Transaction = tran;
                        r += c.ExecuteNonQuery();
                    }

                    tran.Commit();
                }
                catch
                {
                    tran.Rollback(); r = 0;
                }
            }
            Dispose();
            return r;
        }

        /// <summary>
        /// 获取OleDbDataReader
        /// </summary>
        /// <param name="_commandtype"></param>
        /// <param name="_commandtext"></param>
        /// <param name="_parameters"></param>
        /// <returns></returns>
        public OleDbDataReader GetSqlDataReader(CommandType _commandtype, string _commandtext, params OleDbParameter[] _parameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = _commandtype;
            cmd.CommandText = _commandtext;
            cmd.Connection = this.conn;

            if (_parameters != null)
            {
                foreach (OleDbParameter para in _parameters)
                {
                    cmd.Parameters.Add(para);
                }
            }

            try
            {
                OpenConn();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                Function.WriteErrorLog("数据库连接错误或传入空SqlParameter参数，请检查!" + ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 获取DataSet
        /// </summary>
        /// <param name="_commandtype"></param>
        /// <param name="_commandtext"></param>
        /// <param name="_parameters"></param>
        /// <returns></returns>
        public DataSet GetDataSet(CommandType _commandtype, string _commandtext, params OleDbParameter[] _parameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = _commandtype;
            cmd.CommandText = _commandtext;
            cmd.Connection = this.conn;

            foreach (OleDbParameter para in _parameters)
            {
                cmd.Parameters.Add(para);
            }
            OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();

            try
            {
                OpenConn();
                adp.Fill(ds);
                Dispose();
                return ds;
            }
            catch (Exception ex)
            {
                Function.WriteErrorLog("数据库连接错误或传入空OleDbParameter参数，请检查！" + ex.Message);
                return new DataSet();
            }
            finally
            {
                adp.Dispose();
                cmd.Dispose();
                Dispose();
            }

        }

        /// <summary>
        /// ACCESS高效分页
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">分页容量</param>
        /// <param name="strKey">主键</param>
        /// <param name="showString">显示的字段</param>
        /// <param name="queryString">查询字符串，支持联合查询</param>
        /// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param>
        /// <param name="orderString">排序规则</param>
        /// <param name="pageCount">传出参数：总页数统计</param>
        /// <param name="recordCount">传出参数：总记录统计</param>
        /// <returns>装载记录的DataTable</returns>
        public DataTable ExecutePager(int pageIndex, int pageSize, string strKey, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount)
        {
            if (pageIndex < 1) pageIndex = 1;
            if (pageSize < 1) pageSize = 10;
            if (string.IsNullOrEmpty(showString)) showString = "*";
            if (string.IsNullOrEmpty(orderString)) orderString = strKey + " asc ";
            using (OleDbConnection m_Conn = new OleDbConnection(connString))
            {
                m_Conn.Open();
                string myVw = string.Format(" ( {0} ) tempVw ", queryString);
                OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, whereString), m_Conn);

                recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());

                if ((recordCount % pageSize) > 0)
                    pageCount = recordCount / pageSize + 1;
                else
                    pageCount = recordCount / pageSize;
                OleDbCommand cmdRecord;
                if (pageIndex == 1)//第一页
                {
                    cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from (select {1} from {2} {3} order by {4} ) temp", pageSize, showString, myVw, whereString, orderString), m_Conn);
                    //cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString), m_Conn);
                }
                else if (pageIndex > pageCount)//超出总页数
                {
                    cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from (select {1} from {2} {3} order by {4} ) temp ", pageSize, showString, myVw, "where 1=2", orderString), m_Conn);
                }
                else
                {
                    int pageLowerBound = pageSize * pageIndex;
                    int pageUpperBound = pageLowerBound - pageSize;
                    string recordIDs = recordID(string.Format("select top {0} {1} from (select {1} from {2} {3} order by {4} ) temp  ", pageLowerBound, strKey, myVw, whereString, orderString), pageUpperBound);
                    cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw, strKey, recordIDs, orderString), m_Conn);

                }
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
                DataTable dt = new DataTable();
                dataAdapter.Fill(dt);
                m_Conn.Close();
                m_Conn.Dispose();
                return dt;
            }
        }
        /// <summary>
        /// 分页使用
        /// </summary>
        /// <param name="query"></param>
        /// <param name="passCount"></param>
        /// <returns></returns>
        private string recordID(string query, int passCount)
        {
            using (OleDbConnection m_Conn = new OleDbConnection(connString))
            {
                m_Conn.Open();
                OleDbCommand cmd = new OleDbCommand(query, m_Conn);
                string result = string.Empty;
                using (OleDbDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        if (passCount < 1)
                        {
                            result += "," + dr.GetInt32(0);
                        }
                        passCount--;
                    }
                }
                m_Conn.Close();
                m_Conn.Dispose();
                return result.Substring(1);
            }
        }


    }
}
